#!/usr/bin python
# -*- coding: utf-8 -*-
# @Time    : 2024/1/17 15:16
# @Author  : hhr
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT, WD_TABLE_ALIGNMENT
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.oxml.shared import OxmlElement
from docx.oxml.ns import qn, nsdecls
from docx import Document, shared
from docx.oxml import parse_xml
import comtypes.client
import pythoncom, os, shutil, zipfile





class SummaryApprovalDocuments():
    def QueryData(self, start_time, end_time):
        select_sql = f"SELECT A.*, B.real_name FROM `administration`.`personal_center_sealapply` A LEFT JOIN tb_users_message B ON A.apply_user_id=B.id WHERE A.`create_time` >= '{start_time}' AND A.`create_time` <= '{end_time}' AND A.`state` = 'five'"
        self.cur.execute(select_sql)
        select_data = self.cur.fetchall()

        for i in select_data:
            one_dict = {}
            one_dict["apply_user"] = i[-1]                                                          # 申请人
            one_dict["create_time"] = i[1].strftime("%Y-%m-%d %H:%M")                               # 申请时间
            one_dict["matter_type_text"] = i[6]                                                     # 申请事由
            one_dict["servings"] = i[9]                                                             # 用印测试
            one_dict["number"] = i[3]                                                               # 用印编号
            one_dict["seal_name"] = i[13]                                                           # 用印名称
            one_dict["note"] = i[12]                                                                # 备注
            record_sql = f"SELECT A.update_time, A.state, A.audit_note, A.node, B.real_name FROM `administration`.`personal_center_sealapplyauditrecord` A LEFT JOIN tb_users_message B ON A.audit_people_id=B.id WHERE A.`parent_id_id` = '{i[0]}'"
            self.cur.execute(record_sql)
            record_data = self.cur.fetchall()
            record_list = []
            for j in record_data:
                record_list.append({
                    "process_time": j[0].strftime("%Y-%m-%d %H:%M"),                                # 审核时间
                    "process_people": j[4],                              # 审核人
                    "process_node": j[3],                           # 审核节点
                    "process_state": j[1],                      # 审核状态
                    "process_opinion": j[2]                          # 审核意见
                })
            one_dict["record_list"] = record_list
            print(one_dict)
            print()
            print()


    def set_cell_border(self, cell, **kwargs):
        """
        设置单元格的边框
        """
        tc = cell._tc
        tcPr = tc.get_or_add_tcPr()

        tcBorders = tcPr.first_child_found_in("w:tcBorders")
        if tcBorders is None:
            tcBorders = OxmlElement('w:tcBorders')
            tcPr.append(tcBorders)

        for edge in ('left', 'top', 'right', 'bottom', 'insideH', 'insideV'):
            edge_data = kwargs.get(edge)
            if edge_data:
                tag = 'w:{}'.format(edge)

                element = tcBorders.find(qn(tag))
                if element is None:
                    element = OxmlElement(tag)
                    tcBorders.append(element)

                for key in ["sz", "val", "color", "space", "shadow"]:
                    if key in edge_data:
                        element.set(qn('w:{}'.format(key)), str(edge_data[key]))


    def compress_folder(self, source_dir, file_name):
        output_filename = os.getcwd() + f"/media/Summary/{file_name}.zip"
        with zipfile.ZipFile(output_filename, 'w', zipfile.ZIP_DEFLATED) as zf:
            for root, dirs, files in os.walk(source_dir):
                for file in files:
                    # 获取当前文件的路径
                    filepath = os.path.join(root, file)
                    # 将文件添加到压缩包中
                    arcname = os.path.relpath(filepath, source_dir)
                    zf.write(filepath, arcname=arcname)
        return output_filename


    def CreateWordTable(self, data, dir_name):
        save_file_path = os.getcwd() + f"/media/Summary/{dir_name}"
        if not os.path.exists(save_file_path):
            os.makedirs(save_file_path)
        for one_data in data:
            document = Document()                       # 创建一个新的word对象
            # ************************* 设置字体 ***************************
            document.styles['Normal'].font.name = "Times New Roman"
            document.styles['Normal'].element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')

            # ************************* 创建1行4列表格 *************************
            FirstLine = document.add_table(rows=1, cols=4, style="Table Grid")
            FirstLine.alignment = WD_TABLE_ALIGNMENT.CENTER

            # ************************* 表头 *************************
            FirstLine.cell(0, 0).merge(FirstLine.cell(0, 3))            # 合并单元格
            FirsCell = FirstLine.cell(0, 0)
            FirsCell.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER  # 水平居中
            FirsCell.paragraphs[0].add_run("印章/重要证件使用(借用)审批单")
            FirsCell.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            FirstLine.rows[0].height = 457200
            FirstLine.style.font.bold = True
            FirstLine.style.font.name = u'宋体'

            # ************************* 第二行 *************************
            SecondLine = document.add_table(rows=1, cols=4, style="Table Grid")
            SecondLine.alignment = WD_TABLE_ALIGNMENT.CENTER
            SecondLine.cell(0, 0).merge(SecondLine.cell(0, 3))  # 合并单元格
            SecondCell = SecondLine.cell(0, 0)
            SecondCell.paragraphs[0].add_run("申请详情")
            SecondCell.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            SecondLine.rows[0].height = 357200
            SecondLine.style.font.bold = False
            SecondLine.style.font.name = u'宋体'
            SecondLine.style.font.size = shared.Pt(13)
            rows = SecondLine.rows[0]
            for cell in rows.cells:
                shading_elm = parse_xml(r'<w:shd {} w:fill="F5F5F5"/>'.format(nsdecls('w')))
                cell._tc.get_or_add_tcPr().append(shading_elm)

            # ************************* 第三行 *************************
            ThreeLine = document.add_table(rows=1, cols=4, style="Table Grid")
            ThreeLine.alignment = WD_TABLE_ALIGNMENT.CENTER
            ThreeLine.rows[0].height = 357200
            ThreeLine.rows[0].cells[0].width = shared.Cm(5)
            ThreeLine.rows[0].cells[1].width = shared.Cm(9)
            ThreeLine.rows[0].cells[2].width = shared.Cm(5)
            ThreeLine.rows[0].cells[3].width = shared.Cm(9)
            ThreeCell_1 = ThreeLine.cell(0, 0)
            ThreeCell_2 = ThreeLine.cell(0, 1)
            ThreeCell_3 = ThreeLine.cell(0, 2)
            ThreeCell_4 = ThreeLine.cell(0, 3)
            ThreeCell_1.paragraphs[0].add_run("申  请  人")
            ThreeCell_1.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT  # 水平居中
            ThreeCell_1.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            ThreeCell_3.paragraphs[0].add_run("申请日期")
            ThreeCell_3.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT  # 水平居中
            ThreeCell_3.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            ThreeCell_2.paragraphs[0].add_run(one_data["apply_user"])
            ThreeCell_2.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.LEFT  # 水平居中
            ThreeCell_2.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            ThreeCell_4.paragraphs[0].add_run(one_data["create_time"])
            ThreeCell_4.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.LEFT  # 水平居中
            ThreeCell_4.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中

            # ************************* 第四行 *************************
            FourLine = document.add_table(rows=1, cols=4, style="Table Grid")
            FourLine.alignment = WD_TABLE_ALIGNMENT.CENTER
            FourLine.rows[0].height = 357200
            FourLine.rows[0].cells[0].width = shared.Cm(5)
            FourLine.rows[0].cells[1].width = shared.Cm(9)
            FourLine.rows[0].cells[2].width = shared.Cm(5)
            FourLine.rows[0].cells[3].width = shared.Cm(9)
            FourCell_1 = FourLine.cell(0, 0)
            FourCell_2 = FourLine.cell(0, 1)
            FourCell_3 = FourLine.cell(0, 2)
            FourCell_4 = FourLine.cell(0, 3)
            FourCell_1.paragraphs[0].add_run("用印编号")
            FourCell_1.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT  # 水平居中
            FourCell_1.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            FourCell_3.paragraphs[0].add_run("用印次数")
            FourCell_3.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT  # 水平居中
            FourCell_3.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            FourCell_2.paragraphs[0].add_run(one_data["number"])
            FourCell_2.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.LEFT  # 水平居中
            FourCell_2.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            FourCell_4.paragraphs[0].add_run(str(one_data["servings"]))
            FourCell_4.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.LEFT  # 水平居中
            FourCell_4.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中

            # ************************* 第五行 *************************
            FiveLine = document.add_table(rows=1, cols=4, style="Table Grid")
            FiveLine.alignment = WD_TABLE_ALIGNMENT.CENTER
            FiveLine.cell(0, 1).merge(FiveLine.cell(0, 3))  # 合并单元格
            FiveLine.rows[0].height = 357200
            FiveLine.rows[0].cells[0].width = shared.Cm(5)
            FiveLine.rows[0].cells[1].width = shared.Cm(23)
            FiveCell_1 = FiveLine.cell(0, 0)
            FiveCell_2 = FiveLine.cell(0, 1)
            FiveCell_1.paragraphs[0].add_run("用印事由")
            FiveCell_1.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT  # 水平居中
            FiveCell_1.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            FiveCell_2.paragraphs[0].add_run(one_data["matter_type_text"])
            FiveCell_2.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.LEFT  # 水平居中
            FiveCell_2.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中

            # ************************* 第六行 *************************
            SixLine = document.add_table(rows=1, cols=4, style="Table Grid")
            SixLine.alignment = WD_TABLE_ALIGNMENT.CENTER
            SixLine.cell(0, 1).merge(SixLine.cell(0, 3))  # 合并单元格
            SixLine.rows[0].height = 357200
            SixLine.rows[0].cells[0].width = shared.Cm(5)
            SixLine.rows[0].cells[1].width = shared.Cm(23)
            SixCell_1 = SixLine.cell(0, 0)
            SixCell_2 = SixLine.cell(0, 1)
            SixCell_1.paragraphs[0].add_run("""印章/证件名称""")
            SixCell_1.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT  # 水平居中
            SixCell_1.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            SixCell_2.paragraphs[0].add_run(one_data["seal_name"])
            SixCell_2.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.LEFT  # 水平居中
            SixCell_2.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中

            # ************************* 第七行 *************************
            SevenLine = document.add_table(rows=1, cols=4, style="Table Grid")
            SevenLine.alignment = WD_TABLE_ALIGNMENT.CENTER
            SevenLine.cell(0, 1).merge(SevenLine.cell(0, 3))  # 合并单元格
            SevenLine.rows[0].height = 357200
            SevenLine.rows[0].cells[0].width = shared.Cm(5)
            SevenLine.rows[0].cells[1].width = shared.Cm(23)
            SevenCell_1 = SevenLine.cell(0, 0)
            SevenCell_2 = SevenLine.cell(0, 1)
            SevenCell_1.paragraphs[0].add_run("备         注")
            SevenCell_1.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT  # 水平居中
            SevenCell_1.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            SevenCell_2.paragraphs[0].add_run(one_data["note"])
            SevenCell_2.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.LEFT  # 水平居中
            SevenCell_2.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中

            # ************************* 第八行 *************************
            EightLine = document.add_table(rows=1, cols=4, style="Table Grid")
            EightLine.alignment = WD_TABLE_ALIGNMENT.CENTER
            EightLine.cell(0, 0).merge(EightLine.cell(0, 3))  # 合并单元格
            EightCell = EightLine.cell(0, 0)
            EightCell.paragraphs[0].add_run("审批详情")
            EightCell.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
            EightLine.rows[0].height = 357200
            EightLine.style.font.bold = False
            EightLine.style.font.name = u'宋体'
            Eightrows = EightLine.rows[0]
            for cell in Eightrows.cells:
                shading_elm = parse_xml(r'<w:shd {} w:fill="F5F5F5"/>'.format(nsdecls('w')))
                cell._tc.get_or_add_tcPr().append(shading_elm)

            # ************************* 审批 *************************
            for key, val in one_data["ApproveDict"].items():
                ApproveLine = document.add_table(rows=1, cols=4, style="Table Grid")
                ApproveLine.alignment = WD_TABLE_ALIGNMENT.CENTER
                ApproveLine.cell(0, 0).merge(ApproveLine.cell(0, 0))  # 左侧合并单元格
                ApproveCell = ApproveLine.cell(0, 0)
                ApproveCell.paragraphs[0].add_run(one_data["ApproveDict"][key]["node_name"])
                ApproveCell.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT  # 水平居中
                ApproveCell.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER  # 垂直居中
                ApproveLine.style.font.bold = False
                ApproveLine.style.font.name = u'宋体'

                B_number = 0
                for i in range(len(one_data["ApproveDict"][key]['data'])):
                    ApproveLine.cell(0, 1).merge(ApproveLine.cell(0, 3))  # 右侧合并单元格
                    fgld = ApproveLine.cell(0, 1).add_table(rows=2, cols=3)
                    fgld.cell(0, 0).paragraphs[0].add_run(one_data["ApproveDict"][key]['data'][i]['process_state'])
                    fgld.cell(0, 1).merge(fgld.cell(0, 2))
                    fgld.cell(0, 1).paragraphs[0].add_run(one_data["ApproveDict"][key]['data'][i]['process_opinion'])
                    if one_data["ApproveDict"][key]['data'][i]['signature_picture']:
                        run = fgld.cell(1, 0).paragraphs[0].add_run()
                        picture_path = os.getcwd() + one_data["ApproveDict"][key]['data'][i]['signature_picture']
                        picture = run.add_picture(picture_path)
                        picture.height = shared.Cm(1.5)
                        picture.width = shared.Cm(3)
                    else:
                        fgld.cell(1, 0).paragraphs[0].add_run(one_data["ApproveDict"][key]['data'][i]['process_people'])
                    fgld.cell(1, 2).paragraphs[0].add_run(one_data["ApproveDict"][key]['data'][i]['process_time'])
                    fgld.cell(1, 2).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.BOTTOM
                    if B_number != len(one_data["ApproveDict"][key]['data']) - 1:
                        self.set_cell_border(fgld.rows[1].cells[0], bottom={"sz": 8, "val": "dashed", "color": "", "space": "0"})
                        self.set_cell_border(fgld.rows[1].cells[1], bottom={"sz": 8, "val": "dashed", "color": "", "space": "0"})
                        self.set_cell_border(fgld.rows[1].cells[2], bottom={"sz": 8, "val": "dashed", "color": "", "space": "0"})
                    B_number += 1

            document.save(save_file_path+"/word模版1.docx")

            # *********************************** 字体加粗设置 ***********************************
            doc = Document(save_file_path+"/word模版1.docx")
            doc_table = doc.tables[0]
            doc_cell = doc_table.cell(0, 0)
            font = doc_cell.paragraphs[0].runs[0].font
            font.size = shared.Pt(20)
            font.bold = True

            # *********************************** 字体颜色设置 ***********************************
            first_table = doc.tables[1]
            first_cell = first_table.cell(0, 0)
            FirstFont = first_cell.paragraphs[0].runs[0].font
            FirstFont.color.rgb = shared.RGBColor(72, 118, 255)
            seven_table = doc.tables[7]
            seven_cell = seven_table.cell(0, 0)
            SevenFont = seven_cell.paragraphs[0].runs[0].font
            SevenFont.color.rgb = shared.RGBColor(72, 118, 255)
            doc.save(save_file_path+"/example.docx")
            os.remove(save_file_path+"/word模版1.docx")

            # 保存为PDF文件
            pythoncom.CoInitialize()
            word = comtypes.client.CreateObject("Word.Application")
            pythoncom.CoInitialize()
            word.Visiable = 0
            DocFile = word.Documents.Open(save_file_path+"/example.docx")

            save_path = save_file_path + f'/{one_data["number"]}'
            if not os.path.exists(save_path):
                os.makedirs(save_path)
            DocFile.SaveAs(save_path+"/审批单.pdf", FileFormat=17)
            DocFile.Close()
            os.remove(save_file_path+"/example.docx")
            for fujian in one_data["fujian_list"]:
                file_name = os.path.basename(fujian)
                shutil.copy2(fujian, save_path+f"/{file_name}")

        output_filename = self.compress_folder(save_file_path, dir_name)
        return output_filename


